每天5分钟PG聊通透第12期,为什么有的SQL会自动并行?
参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
每天5分钟PG聊通透第12期,为什么SQL会自动启用并行计算?
背景
问题说明(现象、环境) 分析原因 结论和解决办法
链接、驱动、SQL
12、为什么SQL会自动启用并行计算?
https://www.bilibili.com/video/BV1pa41167qZ/
1、参数设置允许并行计算
max_worker_processes > 1
max_parallel_workers > 1
max_parallel_workers_per_gather > 1
enable_parallel_append = on
enable_parallel_hash = on
2、SQL本身支持并行
《PostgreSQL 并行计算解说 汇总》 《parallel 递归查询, 树状查询, 异构查询, CTE, recursive CTE, connect by》 《parallel CREATE INDEX CONCURRENTLY - 不堵塞读写》 《parallel subquery》 《parallel gather | gathermerge - enable leader worker process》 《parallel FDW scan (并行访问多个外部表) with parallel append (FDW must with IsForeignScanParallelSafe)》 《parallel CTE (Common Table Express)》 《parallel union all》 《parallel append merge》 《parallel append》 《parallel partition table wise agg》 《parallel partition table wise join》 《parallel hash join》 《parallel merge join》 《parallel nestloop join》 《parallel index only scan》 《parallel bitmap scan》 《parallel index scan》 《parallel OLAP : 中间结果 parallel with unlogged table》 《parallel in rc,rr 隔离级别》 《parallel gather, gather merge》 《parallel 自定义并行函数(UDF)》 《parallel 自定义并行聚合》 《parallel sort》 《parallel create index》 《parallel CREATE MATERIALIZED VIEW》 《parallel create table as》 《parallel select into》 《parallel agg》 《parallel filter》 《parallel seq scan》
3、优化器认为有必要使用并行计算 (表或者索引的大小达到了开启并行的阈值、采用并行的总代价最低) , 自动计算并行度.
parallel_setup_cost = 1000.0
parallel_tuple_cost = 0.1
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
4、强制设置并行度
postgres=# alter table a set (parallel_workers =4);
ALTER TABLE
postgres=# set parallel_setup_cost=0;
SET
postgres=# set parallel_tuple_cost=0;
SET
postgres=# set min_parallel_table_scan_size=0;
SET
postgres=# set min_parallel_index_scan_size=0;
SET
postgres=# set max_parallel_workers_per_gather =4;
SET
postgres=# explain select count(*) from a;
QUERY PLAN
-------------------------------------------------------------------------------------
Finalize Aggregate (cost=11459.02..11459.03 rows=1 width=8)
-> Gather (cost=11459.00..11459.01 rows=4 width=8)
Workers Planned: 4
-> Partial Aggregate (cost=11459.00..11459.01 rows=1 width=8)
-> Parallel Seq Scan on a (cost=0.00..10834.00 rows=250000 width=0)
(5 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from a;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=11459.02..11459.03 rows=1 width=8) (actual time=177.549..179.733 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=8334
-> Gather (cost=11459.00..11459.01 rows=4 width=8) (actual time=177.414..179.723 rows=5 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=8334
-> Partial Aggregate (cost=11459.00..11459.01 rows=1 width=8) (actual time=166.202..166.202 rows=1 loops=5)
Output: PARTIAL count(*)
Buffers: shared hit=8334
Worker 0: actual time=163.956..163.956 rows=1 loops=1
Buffers: shared hit=1671
Worker 1: actual time=163.958..163.958 rows=1 loops=1
Buffers: shared hit=1610
Worker 2: actual time=163.525..163.526 rows=1 loops=1
Buffers: shared hit=1657
Worker 3: actual time=163.678..163.679 rows=1 loops=1
Buffers: shared hit=1651
-> Parallel Seq Scan on public.a (cost=0.00..10834.00 rows=250000 width=0) (actual time=0.432..147.447 rows=200000 loops=5)
Output: id, info
Buffers: shared hit=8334
Worker 0: actual time=0.744..145.298 rows=200440 loops=1
Buffers: shared hit=1671
Worker 1: actual time=0.120..146.276 rows=193200 loops=1
Buffers: shared hit=1610
Worker 2: actual time=0.562..144.802 rows=198840 loops=1
Buffers: shared hit=1657
Worker 3: actual time=0.021..144.991 rows=198120 loops=1
Buffers: shared hit=1651
Planning Time: 0.053 ms
Execution Time: 179.766 ms
(32 rows)
补充说明:
1、PG并行计算的COST只算一个worker的cost, 所以你会发现即使算上启动代价, 并行的代价也可能比非并行更低. 否则优化器就永远不可能使用并行了.
2、OLTP系统建议把并行的启动代价尽量调高, 或者关闭自动并行, 因为OLTP系统对SQL的RT非常敏感, 万一系统中有个外部(DBA或者开发者呀)角色跑了个大查询, 启用并行可能更容易把资源消耗光, 导致业务的高并发小SQL RT抖动影响业务体验.
参考:
《PostgreSQL 并行计算解说 汇总》 《PostgreSQL 11 并行计算算法，参数，强制并行度设置》 《PostgreSQL 9.6 并行计算 优化器算法浅析》
本期彩蛋 - 数据库生态工具&信创开源数据库
用好周边工具, 数据库管理水平战胜90%老司机
1、管控软件
云猿生开源的kubeblocks, 如果你要管理很多套并且种类很多的数据库产品, 推荐选择.
https://github.com/apecloud/kubeblocks
乘数开源的clup, 专门用来管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 企业可以关注一下.
https://www.csudata.com/
若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套数据库, 并且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
海信聚好看的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
Bytebase 的目标非常远大, 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
D-Smart, Oracle老前辈白老大他们搞的, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
通过信创并且开源的数据库:
PolarDB for PostgreSQL
https://github.com/ApsaraDB/PolarDB-for-PostgreSQL
以下PG系国产数据库也非常值得关注: HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、ProtonBase(云原生分布式数仓. https://protonbase.com/ ).
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: